using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.EntityFrameworkCore;
using MySql.Data.MySqlClient;
using UwinEducation.Domain.Entities;
using UwinEducation.Domain.IRepositories;
using UwinEducation.Infrastructure.Persistence;
using UwinEducation.Shared.Enums;
using UwinEducation.Shared.Models;

namespace UwinEducation.Infrastructure.Repositories
{
    /// <summary>
    /// 订单仓储实现
    /// </summary>
    public class OrderRepository : Repository<Order, Guid>, IOrderRepository
    {
        private readonly ApplicationDbContext _context;

        public OrderRepository(ApplicationDbContext context)
            : base(context)
        {
            _context = context;
        }

        /// <summary>
        /// 获取订单
        /// </summary>
        public async Task<Order> GetByIdAsync(Guid id)
        {
            // 基本查询 - 获取订单并加载所有关联数据
            //TODO: 如果学生不存在，则不包含
            var order = await _context
                .Orders.AsNoTracking()
                .Include(o => o.Student)
                .Where(o => !o.Student.IsDeleted)
                .Include(o => o.OrderDetails)
                .ThenInclude(od => od.TeacherSchedule)
                .ThenInclude(ts => ts.Teacher)
                .Include(o => o.OrderDetails)
                .ThenInclude(od => od.TeacherSchedule)
                .ThenInclude(ts => ts.Course)
                .Include(o => o.OrderDetails)
                .ThenInclude(od => od.TeacherSchedule)
                .ThenInclude(ts => ts.Classroom)
                .Include(o => o.OrderDetails)
                .ThenInclude(od => od.StudentScheduleDetails)
                .Include(o => o.OrderDetails)
                .ThenInclude(od => od.StudentScheduleDetails)
                .ThenInclude(ts => ts.TeacherScheduleDetails)
                .Include(o => o.Appointments)
                .ThenInclude(a => a.Teacher)
                .Include(o => o.OrderDiscounts)
                .ThenInclude(d => d.Creator)
                .Include(o => o.OrderPayments)
                .ThenInclude(p => p.Creator)
                .Include(o => o.OrderPayments)
                .ThenInclude(p => p.Auditor)
                .FirstOrDefaultAsync(o => o.Id == id);

            // 如果找到订单且学生存在但已删除，则将学生设置为null
            // if (order != null && order.Student != null && order.Student.IsDeleted)
            // {
            //     order.Student = null;
            // }

            return order;
        }

        /// <summary>
        /// 根据订单编号获取订单
        /// </summary>
        public async Task<Order> GetByOrderNumberAsync(string orderNumber)
        {
            return await _context
                .Orders.Include(o => o.OrderDetails)
                .Include(o => o.OrderDiscounts)
                .Include(o => o.OrderPayments)
                .FirstOrDefaultAsync(o => o.OrderNumber == orderNumber);
        }

        /// <summary>
        /// 获取学生的订单列表
        /// </summary>
        public async Task<List<Order>> GetByStudentIdAsync(Guid studentId)
        {
            return await _context
                .Orders.Include(o => o.Student)
                .Include(o => o.OrderDetails)
                .Include(o => o.OrderDiscounts)
                .Include(o => o.OrderPayments)
                .Where(o => o.StudentId == studentId)
                .OrderByDescending(o => o.CreatedDate)
                .ToListAsync();
        }

        /// <summary>
        /// 根据支付状态获取订单列表
        /// </summary>
        public async Task<List<Order>> GetByPaymentStatusAsync(PaymentStatus paymentStatus)
        {
            return await _context
                .Orders.Include(o => o.Student)
                .Include(o => o.OrderDetails)
                .Include(o => o.OrderDiscounts)
                .Include(o => o.OrderPayments)
                .Where(o => o.PaymentStatus == paymentStatus)
                .OrderByDescending(o => o.CreatedDate)
                .ToListAsync();
        }

        /// <summary>
        /// 获取订单分页列表
        /// </summary>
        public async Task<PagedResult<Order>> GetPagedListAsync(OrderPageRequest pageRequest)
        {
            //TODO: 如果学生不存在，则不包含
            var query = _context
                .Orders.Include(o => o.Student)
                .Where(o => !o.Student.IsDeleted)
                .Include(o => o.OrderDetails)
                .ThenInclude(od => od.TeacherSchedule)
                .Include(o => o.OrderDetails)
                .ThenInclude(od => od.StudentScheduleDetails)
                .Include(o => o.OrderDetails)
                .ThenInclude(od => od.StudentScheduleDetails)
                .ThenInclude(ts => ts.TeacherScheduleDetails)
                .Include(o => o.OrderDiscounts)
                .Include(o => o.OrderPayments)
                .Include(o => o.Appointments)
                .ThenInclude(a => a.Teacher)
                .AsNoTracking() // 添加 AsNoTracking 提升查询性能
                .AsSplitQuery() // 使用分割查询避免笛卡尔积
                .AsQueryable();
            // 筛选条件：创建日期范围
            if (pageRequest.StartDate.HasValue)
            {
                query = query.Where(o => o.CreatedDate.Date >= pageRequest.StartDate.Value.Date);
            }

            if (pageRequest.EndDate.HasValue)
            {
                query = query.Where(o => o.CreatedDate.Date <= pageRequest.EndDate.Value.Date);
            }

            // 筛选条件：学号
            if (!string.IsNullOrEmpty(pageRequest.StudentNo))
            {
                query = query.Where(o => o.Student.StudentNo.Contains(pageRequest.StudentNo));
            }

            // 筛选条件：学生姓名
            if (!string.IsNullOrEmpty(pageRequest.StudentName))
            {
                query = query.Where(o => o.Student.Name.Contains(pageRequest.StudentName));
            }

            // 筛选条件：订单说明
            if (!string.IsNullOrEmpty(pageRequest.Description))
            {
                query = query.Where(o => o.Description.Contains(pageRequest.Description));
            }

            // 筛选条件：支付状态
            if (pageRequest.PaymentStatus.HasValue)
            {
                // 先获取数据到内存中，然后进行计算和筛选
                var orders = await query.ToListAsync();

                var filteredOrders = new List<Order>();

                foreach (var order in orders)
                {
                    // 计算总价
                    decimal totalAmount = order.OrderDetails.Sum(od => od.UnitPrice);
                    // 计算折扣
                    decimal discountAmount = order.OrderDiscounts.Sum(od => od.DiscountAmount);
                    // 计算已支付金额
                    decimal paidAmount = order.OrderPayments.Sum(op => op.Amount);
                    // 计算应付金额（总价 - 折扣 = 应付金额）
                    decimal amountDue = totalAmount - discountAmount;
                    // 计算剩余金额（应付金额 - 已付金额 = 剩余金额）
                    decimal remainingAmount = amountDue - paidAmount;

                    // 确定支付状态
                    PaymentStatus calculatedStatus;
                    if (remainingAmount > 0)
                    {
                        calculatedStatus = PaymentStatus.Unpaid; // 未付款
                    }
                    else if (remainingAmount == 0)
                    {
                        calculatedStatus = PaymentStatus.Paid; // 已付清
                    }
                    else
                    {
                        calculatedStatus = PaymentStatus.PartiallyPaid; // 部分付款
                    }

                    // 更新订单的金额信息（不写入数据库，仅用于返回）
                    order.TotalAmount = totalAmount;
                    order.DiscountedAmount = discountAmount;
                    order.PaidAmount = paidAmount;

                    // 如果支付状态匹配，添加到结果列表
                    if (calculatedStatus == pageRequest.PaymentStatus.Value)
                    {
                        filteredOrders.Add(order);
                    }
                }

                // 计算总记录数
                var _total = filteredOrders.Count;

                // 应用分页
                var _items = filteredOrders
                    .OrderByDescending(o => o.CreatedDate)
                    .Skip((pageRequest.PageIndex - 1) * pageRequest.PageSize)
                    .Take(pageRequest.PageSize)
                    .ToList();

                // 直接返回结果，不再继续后面的查询
                return new PagedResult<Order> { Total = _total, Items = _items };
            }

            //query=query.OrderByDescending(o => o.Student.StudentNo);
            // 计算总记录数
            var total = await query.CountAsync();

            // 获取分页数据
            var items = await query
                .OrderBy(o => o.Student.StudentNo)
                .Skip((pageRequest.PageIndex - 1) * pageRequest.PageSize)
                .Take(pageRequest.PageSize)
                .ToListAsync();

            return new PagedResult<Order> { Total = total, Items = items };
        }

        /// <summary>
        /// 添加订单
        /// </summary>
        public async Task AddAsync(Order order)
        {
            await _context.Orders.AddAsync(order);
            await _context.SaveChangesAsync();
        }

        /// <summary>
        /// 更新订单
        /// </summary>
        public async Task UpdateAsync(Order order)
        {
            _context.Orders.Update(order);
            await _context.SaveChangesAsync();
        }

        /// <summary>
        /// 删除订单
        /// </summary>
        //public async Task DeleteAsync(Guid id)
        //{
        //    var order = await _context.Orders.FindAsync(id);
        //    if (order != null)
        //    {
        //        _context.Orders.Remove(order);
        //        await _context.SaveChangesAsync();
        //    }
        //}

        /// <summary>
        /// 根据学生ID和学期开始时间和结束时间获取订单
        /// </summary>
        public async Task<Order> GetByStudentIdAndSemesterAsync(
            Guid studentId,
            DateTime? semesterStartDate,
            DateTime? semesterEndDate
        )
        {
            return await _context.Orders.FirstOrDefaultAsync(o =>
                o.StudentId == studentId
                && o.CreatedDate >= semesterStartDate
                && o.CreatedDate <= semesterEndDate
            );
        }

        public async Task<PagedResult<Order>> GetPagedListByPaymentStatusAsync(
            PaymentStatus paymentStatus,
            int pageIndex = 1,
            int pageSize = 10
        )
        {
            // 使用SQL直接计算支付状态
            var sql =
                @"
            SELECT o.*, 
                   s.Name AS StudentName, s.StudentNo,
                   (SELECT COALESCE(SUM(od.UnitPrice), 0) FROM order_details od WHERE od.OrderId = o.Id) AS TotalAmount,
                   (SELECT COALESCE(SUM(disc.DiscountAmount), 0) FROM order_discounts disc WHERE disc.OrderId = o.Id) AS DiscountAmount,
                   (SELECT COALESCE(SUM(pay.Amount), 0) FROM order_payments pay WHERE pay.OrderId = o.Id) AS PaidAmount
            FROM orders o
            LEFT JOIN students s ON o.StudentId = s.Id
            HAVING 
                CASE 
                    WHEN (TotalAmount - DiscountAmount - PaidAmount) > 0 THEN @unpaidStatus
                    WHEN (TotalAmount - DiscountAmount - PaidAmount) = 0 THEN @paidStatus
                    ELSE @partiallyPaidStatus
                END = @targetStatus
            ORDER BY o.CreatedDate DESC
            LIMIT @skip, @take";

            var parameters = new List<object>
            {
                new MySqlParameter("@unpaidStatus", (int)PaymentStatus.Unpaid),
                new MySqlParameter("@paidStatus", (int)PaymentStatus.Paid),
                new MySqlParameter("@partiallyPaidStatus", (int)PaymentStatus.PartiallyPaid),
                new MySqlParameter("@targetStatus", (int)paymentStatus),
                new MySqlParameter("@skip", (pageIndex - 1) * pageSize),
                new MySqlParameter("@take", pageSize),
            };

            var orders = await _context.Orders.FromSqlRaw(sql, parameters.ToArray()).ToListAsync();

            // 计算总数
            var countSql =
                @"
            SELECT COUNT(*)
            FROM orders o
            CROSS APPLY (
                SELECT COALESCE(SUM(od.UnitPrice), 0) AS TotalAmount
                FROM order_details od 
                WHERE od.OrderId = o.Id
            ) t1
            CROSS APPLY (
                SELECT COALESCE(SUM(disc.DiscountAmount), 0) AS DiscountAmount
                FROM order_discounts disc 
                WHERE disc.OrderId = o.Id
            ) t2
            CROSS APPLY (
                SELECT COALESCE(SUM(pay.Amount), 0) AS PaidAmount
                FROM order_payments pay 
                WHERE pay.OrderId = o.Id
            ) t3
            WHERE 
                CASE 
                    WHEN (t1.TotalAmount - t2.DiscountAmount - t3.PaidAmount) > 0 THEN @unpaidStatus
                    WHEN (t1.TotalAmount - t2.DiscountAmount - t3.PaidAmount) = 0 THEN @paidStatus
                    ELSE @partiallyPaidStatus
                END = @targetStatus";

            var totalCount = await _context.Database.ExecuteSqlRawAsync(
                countSql,
                parameters.ToArray()
            );

            return new PagedResult<Order> { Total = totalCount, Items = orders };
        }
    }
}
